package com.rickpan.repository;

import com.rickpan.entity.TeamMember;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

/**
 * 团队成员数据访问层
 */
@Repository
public interface TeamMemberRepository extends JpaRepository<TeamMember, Long> {

    /**
     * 根据团队ID和用户ID查找成员
     */
    Optional<TeamMember> findByTeamIdAndUserId(Long teamId, Long userId);

    /**
     * 根据团队ID查找所有成员
     */
    List<TeamMember> findByTeamId(Long teamId);

    /**
     * 根据团队ID分页查找成员
     */
    Page<TeamMember> findByTeamId(Long teamId, Pageable pageable);

    /**
     * 根据用户ID查找所有团队
     */
    List<TeamMember> findByUserId(Long userId);

    /**
     * 根据用户ID查找活跃团队
     */
    @Query("SELECT tm FROM TeamMember tm WHERE tm.userId = :userId AND tm.status = 'ACTIVE'")
    List<TeamMember> findActiveTeamsByUserId(@Param("userId") Long userId);

    /**
     * 检查用户是否为团队成员
     */
    @Query("SELECT COUNT(tm) > 0 FROM TeamMember tm WHERE tm.teamId = :teamId AND tm.userId = :userId AND tm.status = 'ACTIVE'")
    boolean existsByTeamIdAndUserIdAndStatusActive(@Param("teamId") Long teamId, @Param("userId") Long userId);

    /**
     * 检查用户是否为团队管理员
     */
    @Query("SELECT COUNT(tm) > 0 FROM TeamMember tm WHERE tm.teamId = :teamId AND tm.userId = :userId AND tm.role IN ('OWNER', 'ADMIN') AND tm.status = 'ACTIVE'")
    boolean existsByTeamIdAndUserIdAndRoleAdminOrOwner(@Param("teamId") Long teamId, @Param("userId") Long userId);

    /**
     * 检查用户是否为团队所有者
     */
    @Query("SELECT COUNT(tm) > 0 FROM TeamMember tm WHERE tm.teamId = :teamId AND tm.userId = :userId AND tm.role = 'OWNER' AND tm.status = 'ACTIVE'")
    boolean existsByTeamIdAndUserIdAndRoleOwner(@Param("teamId") Long teamId, @Param("userId") Long userId);

    /**
     * 统计团队成员数量
     */
    long countByTeamId(Long teamId);

    /**
     * 统计团队活跃成员数量
     */
    @Query("SELECT COUNT(tm) FROM TeamMember tm WHERE tm.teamId = :teamId AND tm.status = 'ACTIVE'")
    long countActiveByTeamId(@Param("teamId") Long teamId);

    /**
     * 根据团队ID和状态统计成员数量
     */
    long countByTeamIdAndStatus(Long teamId, TeamMember.Status status);

    /**
     * 根据团队ID和状态查找成员
     */
    @Query("SELECT tm FROM TeamMember tm WHERE tm.teamId = :teamId AND tm.status = :status")
    List<TeamMember> findByTeamIdAndStatus(@Param("teamId") Long teamId, @Param("status") TeamMember.Status status);

    /**
     * 根据用户ID和角色查找团队
     */
    @Query("SELECT tm FROM TeamMember tm WHERE tm.userId = :userId AND tm.role = :role AND tm.status = 'ACTIVE'")
    List<TeamMember> findByUserIdAndRole(@Param("userId") Long userId, @Param("role") TeamMember.Role role);

    /**
     * 删除团队成员
     */
    void deleteByTeamIdAndUserId(Long teamId, Long userId);

    /**
     * 根据团队ID查找所有活跃成员
     */
    @Query("SELECT tm FROM TeamMember tm WHERE tm.teamId = :teamId AND tm.status = 'ACTIVE' ORDER BY tm.role ASC, tm.joinedAt ASC")
    List<TeamMember> findActiveByTeamIdOrderByRole(@Param("teamId") Long teamId);

    /**
     * 根据用户ID查找管理的团队
     */
    @Query("SELECT tm FROM TeamMember tm WHERE tm.userId = :userId AND tm.role IN ('OWNER', 'ADMIN') AND tm.status = 'ACTIVE'")
    List<TeamMember> findManagedTeamsByUserId(@Param("userId") Long userId);
}
